# (no interpreter)
# -*- coding: utf8 -*-
# 
# Copyright 2007 Joe Friedrichsen <pengi.films@gmail.com>
# 
# This file is part of bbgun.
# 
# bbgun is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
# 
# bbgun is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with bbgun; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA

"""Define databases used in bbgun

"""

import sys
import re
from UserDict import UserDict

try:
    from bbgun import BBCode
except ImportError:
    print "Cannot find bbgun module BBCode! Please install it and try again."
    sys.exit(1)

class phpbb2wpError(Exception): pass
class InvalidPostIDError(phpbb2wpError): pass
class InvalidPosterIDError(phpbb2wpError): pass
class InvalidTopicIDError(phpbb2wpError): pass
class FieldSizeError(phpbb2wpError): pass
class SQLSelectError(phpbb2wpError): pass
class SQLInsertError(phpbb2wpError): pass
class MissingSubstitutionError(phpbb2wpError): pass

class MySQLdb:
    """A rudimentary MySQL database class
    
    Simply put, this is a thin wrapper for connecting to a MySQL database. The
    classes that are based on this class define more usable methods for inter-
    acting with the specific kinds of databases encountered here: PHP Nuke and
    WordPress databases.
    
    """
    def __init__(self, db_name, host, user, passwd):
        """Connect to a MySQL database
        
        Keyword args:
        db_name -- the name of database
        host -- the name of the host that the database is on
        user -- a username with SELECT and INSERT privileges on the database
        passwd -- the user's password for the database
        
        """
        try:
            import MySQLdb as sql
        except ImportError:
            print "Cannot find Python module 'MySQLdb' (http://sourceforge.net/projects/mysql-python). Please install it and try again."
            sys.exit(1)
        
        self.db = sql.connect(host, user, passwd, db_name)
        self.cursor = self.db.cursor()
        
    def close(self):
        self.cursor.close()
        self.db.close()

class PHPNukeDB(MySQLdb):
    """Database class for pulling info from a PHP Nuke database
    
    """
    def pullPost(self, id, authors='ALL'):
        """Pull a single post by certain authors from a PHP Nuke database
        
        Keyword args:
        id -- PHP Nuke's id number for the post (found in URLs)
        authors -- a list PHP Nuke author names (default == all authors)
        
        Returns:
        A BBPost object
            
        """
        me = 'pullPost'
        
        # Get the post time and author ID number
        if self.cursor.execute("""SELECT post_time, poster_id FROM nuke_bbposts WHERE post_id = %s""", (id, )):
            time, poster_id = self.cursor.fetchone()
            print "%s: post num = %s" % (me, id)
            print "%s: time = %s" % (me, time)
            print "%s: author = %s" % (me, poster_id)
        else:
            raise InvalidPostIDError, "Cannot find post with post_id: %s" % id

        
        # Get the author name
        if self.cursor.execute("""SELECT username FROM nuke_users WHERE user_id = %s""", (poster_id, )):
            author, = self.cursor.fetchone()
            print "%s: with name = %s" % (me, author)
        else:
            raise InvalidPosterIDError, "Cannot author name with poster_id: %s" % poster_id
        
        # Make sure the post is written by the wanted authors
        if authors == 'ALL' or author in authors:
            pass
        else:
            return None
        
        # Get the subject and text
        if self.cursor.execute("""SELECT post_subject, post_text FROM nuke_bbposts_text WHERE post_id = %s""", (id, )):
            subject, text = self.cursor.fetchone()
            print "%s: subject = %s" % (me, subject)
            print "%s: post text = %s" % (me, text)
        else:
            raise InvalidPostIDError, "Cannot find post with post_id: %s" % id
        
        return BBPost(id, subject, text, time, author)
    
    def pullThread(self, topic_id, authors='ALL'):
        """Pull the thread posts by certain authors from a PHP Nuke database
        
        Keyword args:
        topic_id -- PHP Nuke's id number for the topic (found in URLs)
        authors -- a list of PHP Nuke authors (defalt == all)
        
        Returns:
        A list of BBPost objects, ordered according to date (first to last)
        
        """
        me = 'pullThread'
        
        # Grab the post_ids in the thread
        if self.cursor.execute("""SELECT post_id FROM nuke_bbposts WHERE topic_id = %s""", (topic_id, )):
            ids = self.cursor.fetchall()
        else:
            raise InvalidTopicIDError, "Cannot find topic_id: %s" % topic_id
        
        # Unroll the post_ids, and put them in a list
        post_ids = [id[0] for id in ids]
        print "%s: thread = %s" % (me, topic_id)
        print "%s: has post ids = %s" % (me, post_ids)
        
        # Pull those posts
        thread_posts = []
        for id in post_ids:
            next_post = self.pullPost(id, authors)
            
            if next_post:
                # If the first post has a subject, remember it
                if len(thread_posts) == 0:
                    thread_subject = next_post['subject']
                # If the post has a subject, use it
                if next_post['subject']:
                    pass
                # Else: create a subject if there wasn't one
                else:
                    post_number = len(thread_posts) + 1
                    # If the thread had a subject earlier, use '$THREAD_SUBJECT - Part $NUM'
                    if 'thread_subject' in locals():
                        next_post['subject'] = "%s - Part %d" % (thread_subject, post_number)
                    # Else: use 'Untitled Post $NUM'
                    else:
                        next_post['subject'] = "Untitled Post %d" % post_number
                    print "%s: new subj : %s" % (me, next_post['subject'])
                
                thread_posts.append(next_post)
        
        return thread_posts
    
class WordPressDB(MySQLdb):
    """Database class for pushing info to and pulling info from
    a WordPress database.
    
    """
    def __init__(self, db_name, host, user, passwd):
        """Create a WordPressDB object

        """
        MySQLdb.__init__(self, db_name, host, user, passwd)
        self.cache = {}
        self.cache['author_name'] = ''
        self.cache['author_id'] = ''
        self.cache['category_name'] = ''
        self.cache['category_id'] = ''

    def getCatID(self, author_id, category):
        """Get the WordPress ID for a category

        Keyword args:
        author_id -- the id of author who has the category
        category -- the name of the category

        Returns:
        The ID number for the category

        """
        me = 'getCatID'
        categories_table = "wp_%s_categories" % author_id
        
        # See if we know the answer already
        if category == self.cache['category_name']:
            id = self.cache['category_id']
            print "%s: [cached] id for category '%s' = %s" % (me, category, id)
        
        else:
            # Get the ID for the category according to its name
            my_query = "SELECT cat_ID FROM %s WHERE  cat_name = %%s" % categories_table
            data = (category, )
            if self.cursor.execute(my_query, data):
                id, = self.cursor.fetchone()
                print "%s: id for category '%s' = %s" % (me, category, id)
                # Store the hit in the cache
                self.cache['category_name'] = category
                self.cache['category_id'] = id
            else:
                raise SQLSelectError, "Cannot select cat_ID for category '%s'" % category
            
        return id
        
    def getAuthorID(self, name):
        """Get the WordPress ID for an author

        Keyword args:
        name -- the name of the author

        Returns:
        The ID number for the author

        """
        me = 'getAuthorID'

        # See if we know the answer already
        if name == self.cache["author_name"]:
            id = self.cache["author_id"]
            print "%s: [cached] id for author '%s' = %s" % (me, name, id)
            
        else:
            # Get the ID for the author according to his display_name
            if self.cursor.execute("""SELECT ID FROM wp_users WHERE display_name = %s""", (name, )):
                id, = self.cursor.fetchone()
                print "%s: id for author '%s' = %s" % (me, name, id)
                # Store the hit in the cache
                self.cache["author_name"] = name
                self.cache["author_id"] = id
            else:
                raise SQLSelectError, "Cannot select ID for author with display_name: %s" % name

        return id
    
    def authorExists(self, name):
        """See if an author exists in the database

        Keyword args:
        name -- the name of the author to check for

        Returns:
        True if author exists in the database
        False if author does not exist in the database

        """
        me = 'authorExists'
        # Get the WP id that belongs to the author as a test of existance
        try:
            id = self.getAuthorID(name)
        except SQLSelectError:
            print "%s: author '%s' does not exist in the database" % (me, name)
            return False
        else:
            print "%s: author '%s' exists with id = %s" % (me, name, id)
            return True

    def getLastPostID(self, author):
        """Get the last post ID for an author

        Keyword args:
        author -- the id of the author

        Returns:
        the id of the author's last post

        """
        me = 'getLastPostID'

        posts_table = "wp_%s_posts" % author

        my_query = "SELECT ID FROM %s ORDER BY ID DESC" % posts_table
        if self.cursor.execute(my_query):
            id, = self.cursor.fetchone()
            print "%s: last post for author %s = %s" % (me, author, id)
        else:
            raise SQLSelectError, "Cannot get the last post for author '%s'" % author

        return id
    
    def getDefaults(self, author):
        """Get the WordPress defaults for an author
        
        Keyword args:
        author -- the name of the WordPress author
        
        Returns:
        A WPdefaults object
        
        """
        me = "getDefaults"
        
        # Get the WP id that belongs to the author
        author_id = self.getAuthorID(author)
        
        # Build the WordPress table names
        table_prefix = 'wp_%s_' % author_id
        options_table = table_prefix + 'options'
        posts_table = table_prefix + 'posts'
        categories_table = table_prefix + 'categories'
        post2cat_table = table_prefix + 'post2cat'
        
        # From the wp_N_options table, get:
        #   siteurl and permalink_structure for building the guid
        #   default_comment and ping_status for new posts
        #   default_category_id for new posts
        my_query = "SELECT option_value FROM %s WHERE option_name IN (%%s, %%s, %%s, %%s, %%s) ORDER BY option_id" % options_table
        data = ('siteurl', 'permalink_structure', 'default_ping_status',
                'default_comment_status', 'default_category')
        if self.cursor.execute(my_query, data):
            url, category_id, comment, ping, permalink = (x[0] for x in self.cursor.fetchall())
            # Remove any trailing slash
            if url.endswith('/'): url = url[:-1]
        else:
            raise SQLSelectError, "Cannot get default options from database"
        
        # From the wp_N_categories table, get:
        #   default_category name
        my_query = "SELECT cat_name FROM %s WHERE cat_ID = %%s" % categories_table
        data = (category_id, )
        if self.cursor.execute(my_query, data):
            category, = self.cursor.fetchone()        
        else:
            raise SQLSelectError, "Cannot select cat_name for default_category"
        
        defaults = WPdefaults(author, author_id, url, permalink, comment, ping, category)
        print "%s: default_category_id = %s" % (me, category_id)
        for k,v in defaults.items():
            print "%s: %s = %s" % (me, k, v)
        
        return defaults

    def pushThread(self, posts, defaults, categories):
        """Push a thread to the database under the given categories

        Keyword args:
        posts -- a list of WPpost objects
        defaults -- the defaults for the WP author
        categories -- a list of category names to add the posts to

        """
        me = 'pushThread'
        num_posts = len(posts)
        for post in posts:
            cur_post_num = posts.index(post) + 1
            print "%s: pushing post %d of %d" % (me, cur_post_num, num_posts)
            self.pushPost(post, defaults, categories)
    
    def pushPost(self, post, defaults, categories):
        """Push a single post to the database under the given categories

        Keyword args:
        post -- a WPpost object
        defaults -- the defaults for the WP author
        categories -- a list of category names to add the post to
        
        """
        me = 'pushPost'
        
        # Set up database table names - TODO: look into the redundancy of this [[cache?]]
        table_prefix = "wp_%s_" % post['post_author']
        posts_table = table_prefix + "posts"
        categories_table = table_prefix + "categories"
        post2cat_table = table_prefix + "post2cat"
        options_table = table_prefix + "options"
        print "%s: table names = %s, %s, %s" % (me, posts_table, categories_table, post2cat_table)
        
        ### Set up the wp_N_posts fields for each table in a dictionary
        ## wp_N_posts
        for k,v in post.items():
            print "%s: (%s) %s = %s" % (me, posts_table, k, v)
        ## TODO: implement method
        #insertIntoTable(table=posts_table, data=post, mode=INSERT)

        # My situation
        my_query = "INSERT INTO %s SET " % posts_table + ','.join(["%s=%%(%s)s" % (k,k) for k in post.keys()])
        print "%s: posts query = '%s'" % (me, my_query)
        print "%s: data = " % me, dict(post)
        
        self.cursor.execute(my_query, dict(post))
        
        for category in categories:
            ## wp_N_categories
            cats = {}
            # Default category
            if category == 'DEFAULT':
                cats['cat_name'] = defaults['category']
                cats['cat_ID'] = self.getCatID(post['post_author'], cats['cat_name'])
            else:
                cats['cat_name'] = category
                cats['cat_ID'] = self.getCatID(post['post_author'], category)
            #incrementField(table=categories_table, field='category_count', condition="cat_ID="+categories['cat_ID'])
            my_query = "UPDATE %s SET " % categories_table + "%s=%s+1" % ('category_count', 'category_count') + " WHERE %s=%%(%s)s" % ('cat_name', 'cat_name')
            print "%s: categories query = '%s'" % (me, my_query) % cats
            self.cursor.execute(my_query, cats)
            
            ## wp_N_post2cat
            post2cat = {}
            post2cat['post_id'] = self.getLastPostID(post['post_author'])
            post2cat['category_id'] = cats['cat_ID']
            #insertIntoTable(table=post2cat, data=post2cat, mode=INSERT)
            my_query = "INSERT INTO %s SET post_id=%%(%s)s,category_id=%%(%s)s" % (post2cat_table, 'post_id', 'category_id')
            print "pushPost: post2cat query = '%s'" % my_query % post2cat
            self.cursor.execute(my_query, post2cat)

        ## wp_N_options
        options = {}
        options['option_name'] = 'post_count'
        #self.incrementField(table=options, field='option_value', condition="option_name='post_count'")
        my_query = "UPDATE %s SET " % options_table + "%s=%s+1" % ('option_value', 'option_value') + " WHERE %s=%%(%s)s" % ('option_name', 'option_name')
        print "%s: options query = '%s'" % (me, my_query) % options
        self.cursor.execute(my_query, options)

class WPdefaults(UserDict):
    """Store default blog settings for a WordPress author

    """

    def __init__(self, auth_name, auth_id, url, permalink, comment, ping, cat):
        """Create a new WPdefaults object

        Keyword args:
        auth_name -- the name of the author that has these defaults
        auth_id -- the WordPress id for the author
        url -- the URL for the WordPress blog (stored in options table)
        permalink -- the permalink format (stored in options table)
        comment -- the comment status (stored in options table)
        ping -- the pink status (stored in options table)
        cat -- the name of the default category (stored in categories table)

        """
        UserDict.__init__(self)
        self['author_name'] = auth_name
        self['author_id'] = auth_id
        self['url'] = url
        self['permalink'] = permalink
        self['comment'] = comment
        self['ping'] = ping
        self['category'] = cat

class BBPost(UserDict):
    """A bbCode post, with keys: subject, text, date, author
    
    """
    def __init__(self, id, subject, text, date, author):
        """Create a new bbCode post object
        
        Keyword args:
        id -- the post's id number (found in URLs)
        subject -- the subject of the post
        text -- the text of the post
        date -- the time that the post was made
        author -- the name of the author who made the post
        
        """
        UserDict.__init__(self)
        self['id'] = id
        self['subject'] = subject
        self['text'] = text
        self['date'] = date
        self['author'] = author

class WPPost(UserDict):
    """WordPress post
    
    Upon instantiation, all conversions from bbCode to WordPress are done.
    
    """

    def __init__(self, bbpost, defaults):
        """Convert a phpbb post to a WordPress post
        
        Keyword arguments:
        bbpost -- a BBpost object (created by WordPressDB.pullPost)
        defaults -- a WPdefaults object (created by WordPressDB.getDefaults)
        
        # TODO: move to config file
        BBCODE_STRICT -- either 0 or 1. 0 means conform conversions to 
            WordPress/xhtml-strict standards, and lose some bbCode formatting.
            1 means keep all bbCode formatting, breaking some WordPress/xhtml-
            strict standards. BBCODE_STRICT is 1 by default.
        
        """
        UserDict.__init__(self)
        
        from time import time
        me = 'WPPost'
        
        print "%s: BBPost has id: %s" % (me, bbpost['id'])
        
        # TODO: abstract the locale. config? keyword arg?
        self['post_date'] = self.do_date(bbpost['date'], 'America/Denver')
        self['post_date_gmt'] = self.do_date(bbpost['date'])
        
        # TODO: add 'now' as a possible keyword arg for epoch_time
        now = time()
        self['post_modified'] = self.do_date(now, 'America/Denver')
        self['post_modified_gmt'] = self.do_date(now)
        
        #self.BBCODE_STRICT = BBCODE_STRICT
        self['post_content'] = BBCode.to_xhtml(bbpost['text'])
        print "%s: content size = %s" % (me, len(self['post_content']))
        
        # Check the length for the WordPress database
        if len(self['post_content']) > 2**32:
            raise FieldSizeError, "Post is too long for WordPress database: %d (max = %d)" % \
                (len(self['post_content']), 2**32)
        
        self['post_title'] = bbpost['subject']
        print "%s: title size = %s" % (me, len(self['post_title']))
        
        # Check the length for the WordPress database
        if len(self['post_title']) > 2**16:
            raise FieldSizeError, "Title is too long for WordPress database: %d (max = %d)" % \
                (len(self['post_title']), 2**16)
        
        self['post_name'] = self.do_name(self['post_title'])
        self['guid'] = self.do_guid(defaults['url'], defaults['permalink'])
        self['post_author'] = defaults['author_id']

        # Other 'known to be default' values
        self['post_category'] = 0
        self['post_excerpt'] = ''
        self['post_status'] = 'publish'     # TODO: Maybe config?
        self['comment_status'] = defaults['comment']
        self['ping_status'] = defaults['ping']
        self['post_password'] = ''
        self['to_ping'] = ''
        self['pinged'] = ''
        self['post_content_filtered'] = ''
        self['menu_order'] = 0
        self['post_type'] = 'post'          # TODO: Maybe config?
        self['post_mime_type'] = ''
        self['comment_count'] = 0

        for k,v in self.items():
            print "%s: %s = %s" % (me, k, v)

    def do_date(self, epoch_time, time_zone='UTC'):
        """Convert Epoch time format to WordPress' format (in local time)
        
        Keyword args:
        epoch_time -- the time a post was made as measured in secs from Epoch
        time_zone -- the local timezone (as found in /usr/share/zoneinfo)
        
        Returns:
        The WP-format time YYYY-MM-DD HH:mm:ss as a string (in local time)
        
        Examples:
        wp_post_date_gmt = do_date(phpbb_post_time)
        wp_post_date = do_date(phpbb_post_time, "America/Denver")
       
        >>> do_date(1153748686)
        '2006-07-24 13:44:46'
        >>> do_date(1153748686, 'America/Denver')
        '2006-07-24 07:44:46'
        
        phpbb stores the time posts were made as the number of seconds since the
        start of the Epoch, which is defined to begin at 1970-01-01 00:00:00 UTC. 
        WordPress, on the other hand, stores the time posts were made in a more 
        human-readable format: YYYY-MM-DD HH:mm:ss. This method takes a phpbb time
        format (seconds from Epoch) and returns a string in the WordPress format, 
        taking into account the local time zone settings (UTC offset and Daylight
        Savings Time).
        
        """
        from time import strftime, localtime
        from os import environ

        me = 'do_date'
        
        WP_TIME_FORMAT = "%Y-%m-%d %H:%M:%S"
        environ['TZ'] = time_zone
        
        cal_time = strftime(WP_TIME_FORMAT, localtime(epoch_time))
        print "%s: epoch time = %s" % (me, epoch_time)
        print "%s: calendar time = %s" % (me, cal_time)
        
        return cal_time

    def do_name(self, title):
        """Build a WordPress name from a WP title
        
        Keyword args:
        title -- the title of the WordPress post
        
        Returns:
        A string that meets the WP 'post_name' specs:
            all letters made lower-case
            all non-alphanumeric characters deleted
            all spaces replaced by hyphens
        
        """
        me = 'do_name'
        name = re.sub('[^a-z0-9 -]', '', title.lower()).replace(' ', '-')
        print "%s: post name = %s" % (me, name)
        print "%s: length = %s" % (me, len(name))

        # Check the length for the WordPress database
        if len(name) <= 200:
            return name
        else:
            raise FieldSizeError, "Post name is too long for WordPress database: %d (max = %d)" % \
                (len(name), 200)

    def do_guid(self, url, permalink):
        """Build a WordPress guid from a WP title and date
        
        Keyword args:
        date -- the date the post was made (WordPress format)
        name -- the name for the post (WordPress format)
        
        Returns:
        A string that meets the WP 'guid' specs:
            * blog base URL
            * the post's date
            * the post's name
        """
        me = 'do_guid'

        # Get the format fields for the permalink
        fields = [field for field in permalink.split('/') if field]
        print "%s: format fields = %s" % (me, ', '.join(fields))

        # Replace them according to their content
        FORMAT = """
            ^%      # A format macro starts with a '%'
            (.*)    # GROUP: the macro name
            %$      # and ends with a '%'
            """
        format = re.compile(FORMAT, re.VERBOSE)
        fields = [format.sub(self._handle_format, field) for field in fields]
        print "%s: formatted fields = %s" % (me, ', '.join(fields))

        guid = '/'.join([url] + fields)
        print "%s: guid = %s" % (me, guid)
        print "%s: length = %s" % (me, len(guid))

        # Check the length for the WordPress database
        if len(guid) <= 255:
            return guid
        else:
            raise FieldSizeError, "guid is too long for WordPress database: %d (max = %d)" % \
                (len(guid), 255)

    def _handle_format(self, match):
        """Find the right method for a guid field format macro
        
        Keyword args:
        match -- a match object with the following useful attribute:
        groups() -- a list of the groups in the matched string
        
        """
        format = match.groups()[0].lower()
        
        try:
            return getattr(self, '_do_' + format)(match)
        except KeyError:
            raise MissingSubstitutionError, "No method for field: %s" % format

    def _do_year(self, match):
        """Substitute the year in the guid format string

        """
        return self._get_ymd()[0]

    def _do_monthnum(self, match):
        """Substitute the month in the guid format string

        """
        return self._get_ymd()[1]

    def _do_day(self, match):
        """Substitute the day in the guid format string

        """
        return self._get_ymd()[2]

    def _do_postname(self, match):
        """Substitute the name of the post in the guid format string

        """
        return self['post_name']

    def _get_ymd(self):
        """Extract the year, month, day parts from a date

        Returns:
        A list of the form: ['YYYY', 'MM', 'DD']

        """
        date = self['post_date'].split(' ')[0]
        return date.split('-')

class WPComment:
    """
    
    """